﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using SMS.Object;

namespace SMS
{
    public partial class fTonKho : Form
    {
        fLogin main;
        //TenXe objTenXe = new TenXe();
        //MauXe objMauXe = new MauXe();
        string StrConn ;
        public fTonKho(fLogin main1)
        {
            InitializeComponent();
            main = main1;
            StrConn = main.ConnectDB;
        }

        private DataTable show()
        {
            // show sau khi chạy
            SqlDataAdapter danhsachxe;
            DataTable dt = new DataTable();
            SqlConnection con = main.KetNoiDB.GetConServer();
            //da = new SqlDataAdapter("SELECT * FROM NGUOIDUNG ", con);
            danhsachxe = new SqlDataAdapter("SELECT XEMAY.SOKHUNG as 'Số khung',TENLOAIXE as 'Tên Loại Xe',TENMAUXE as 'Tên Màu Xe',SOMAY as 'Số máy',CTXEMAY.GIA as 'Giá' FROM XEMAY ,CTXEMAY , MAUXE , LOAIXE  WHERE XEMAY.SOKHUNG= CTXEMAY.SOKHUNG and CTXEMAY.MAQTLOAIXE = LOAIXE.MAQTLOAIXE and CTXEMAY.MAMAUXE = MAUXE.MAMAUXE and XEMAY.CMND is null ", con);
            dt.Clear();
            danhsachxe.Fill(dt); // do ket qua truy van vao ban
            return dt;
        }
        
        private void bntHuy_Click(object sender, EventArgs e)
        {
            this.Visible = false;
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
          //  dataGridView1.DataSource = show();
        }

        private void fTonKho_Load(object sender, EventArgs e)
        {
            dataGridView1.DataSource = show();
            txtTongTonKho.Text = SoLuongTonKho().ToString();
            //MessageBox.Show(SoLuongTonKho().ToString());
            cboLoaiXe.DataSource = main.Tenxe.laytenloaixetonkho();
            cboLoaiXe.DisplayMember = "TENLOAIXE";
            cboMauXe.DataSource = main.Mauxe.laytenmauxetonkho();
            cboMauXe.DisplayMember = "TENMAUXE";

        }

        private int SoLuongTonKho()
        {
            int result = 0;
            SqlConnection conn = new SqlConnection(main.ConnectDB);
            SqlCommand command = new SqlCommand();
            command.Connection = conn;
            command.CommandText = "SELECT COUNT(*) FROM XEMAY WHERE CMND is null";
            // command.Parameters.Add(new SqlParameter("@TR", "0"));
            try
            {
                conn.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    result = int.Parse(reader[0].ToString());
                    //dem so luong trong mot cot
                }
            }
            catch (SqlException ex)
            {
                string error = ex.Message;
            }
            finally
            {
                command.Dispose();
                conn.Close();
            }
            return result;
        }

        private void bntExport_Click(object sender, EventArgs e)
        {
            try
            {

                // creating Excel Application

                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

                // creating new WorkBook within Excel application

                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                // creating new Excelsheet in workbook
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                // see the excel sheet behind the program
                app.Visible = true;
                // get the reference of first sheet. By default its name is Sheet1.
                // store its reference to worksheet
                worksheet = workbook.Sheets["Sheet1"];
                worksheet = workbook.ActiveSheet;
                // changing the name of active sheet
                worksheet.Name = "Exported from gridview";
                // storing header part in Excel
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }
                // storing Each row and column value to excel sheet
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }
                string currentDir = Environment.CurrentDirectory;

                // save the application

                workbook.SaveAs(currentDir + "\\Danhsachtonkho.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                MessageBox.Show("File xuất ra có đường dẫn: " + currentDir + "\\Danhsachtonkho.xls");


                // Exit from the application

                app.Quit();
            }
            catch(Exception ex)
            {
                MessageBox.Show("Lỗi lưu file");
            }
        }

 

        private void bntTimKiem_Click(object sender, EventArgs e)
        {
            int count;
            if (cboLoaiXe.Text == "Chọn tất cả" && cboMauXe.Text == "Chọn tất cả")
            {
                dataGridView1.DataSource = show();
                count = dataGridView1.RowCount - 1;
                textBox1.Text = count.ToString();
                if (count == 0)
                {
                    MessageBox.Show("Không tìm thấy xe!");
                }
                
            }
            if (cboLoaiXe.Text != "Chọn tất cả" && cboMauXe.Text == "Chọn tất cả")
            {
                DataTable table = new DataTable();
                SqlDataAdapter adp;
                SqlConnection conn = new SqlConnection(main.ConnectDB);
                SqlCommand command = new SqlCommand("SP_SELECT_LOAIXE",conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = conn;
                command.Parameters.AddWithValue("@TENLOAIXE",cboLoaiXe.Text.ToString());
                //command.Parameters.AddWithValue("@TENMAUXE",cboLoaiXe.Text.ToString());
                adp = new SqlDataAdapter(command);
                try
                {    
                    conn.Open();    
                    adp.Fill(table);
                    dataGridView1.DataSource = table;
                    count = dataGridView1.RowCount - 1;
                    textBox1.Text = count.ToString();
                    if (count == 0)
                    {
                        MessageBox.Show("Không tìm thấy xe!");
                    }
                }
                catch (SqlException ex)
                {
               
                    MessageBox.Show("Lỗi tìm kiếm");

                }
                finally
                {
                    conn.Close();
                }
 
            }
            if (cboLoaiXe.Text == "Chọn tất cả" && cboMauXe.Text != "Chọn tất cả")
            {
                DataTable table = new DataTable();
                SqlDataAdapter adp;
                SqlConnection conn = new SqlConnection(main.ConnectDB);
                SqlCommand command = new SqlCommand("SP_SELECT_Mauxe", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = conn;
                command.Parameters.AddWithValue("@TENMAUXE", cboMauXe.Text.ToString());
                //command.Parameters.AddWithValue("@TENMAUXE",cboLoaiXe.Text.ToString());
                adp = new SqlDataAdapter(command);
                try
                {
                    conn.Open();
                    adp.Fill(table);
                    dataGridView1.DataSource = table;
                    count = dataGridView1.RowCount - 1;
                    textBox1.Text = count.ToString();
                    if (count == 0)
                    {
                        MessageBox.Show("Không tìm thấy xe!");
                    }
                }
                catch (SqlException ex)
                {

                    MessageBox.Show("Lỗi tìm kiếm");

                }
                finally
                {
                    conn.Close();
                }

            }
            if (cboLoaiXe.Text != "Chọn tất cả" && cboMauXe.Text != "Chọn tất cả")
            {
                DataTable table = new DataTable();
                SqlDataAdapter adp;
                SqlConnection conn = new SqlConnection(main.ConnectDB);
                SqlCommand command = new SqlCommand("SP_SELECT_Mauxe_Loaixe", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = conn;
                command.Parameters.AddWithValue("@TENLOAIXE", cboLoaiXe.Text.ToString());
                command.Parameters.AddWithValue("@TENMAUXE",cboMauXe.Text.ToString());
                adp = new SqlDataAdapter(command);
                try
                {
                    conn.Open();
                    adp.Fill(table);
                    dataGridView1.DataSource = table;
                    count = dataGridView1.RowCount - 1;
                    textBox1.Text = count.ToString();
                    if (count == 0)
                    {
                        MessageBox.Show("Không tìm thấy xe!");
                    }
                    
                }
                catch (SqlException ex)
                {

                    MessageBox.Show("Lỗi tìm kiếm");

                }
                finally
                {
                    conn.Close();
                }
            }           
        }
    }
}
